﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_LABEL_Get_Label_Using_Pgm_List')
	BEGIN
		DROP Procedure usp_UPDMS_COM_LABEL_Get_Label_Using_Pgm_List
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_LABEL_Get_Label_Using_Pgm_List
**	Desc : 특정 라벨을 사용중인 프로그램 조회 (라벨삭제시 체크용)
**	Test Exec Query : Exec usp_UPDMS_COM_LABEL_Get_Label_Using_Pgm_List 3, 'ko'
**	Called by : Com_Dac_UPDMS_COM_LABEL.cs
**	Program ID : Com1017m
**	Auth : 송시명
**	Date : 2011-02-25
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**						
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_LABEL_Get_Label_Using_Pgm_List]
@li_label_id int,
@ls_lang_set nvarchar(2)
AS
SET NOCOUNT ON

SELECT Y.Label_Nm,
       CASE Y.Div WHEN 'L' THEN 'LITERAL' ELSE 'GRID' END Div,
       Y.Pgm_Id,
       CASE @ls_lang_set WHEN 'ko' THEN X.Pgm_Nm ELSE X.Pgm_Nm_Eng END Pgm_Nm
  FROM UPDMS_COM_PGM AS X WITH(NOLOCK)
  JOIN (
       SELECT DISTINCT CASE @ls_lang_set WHEN 'ko' THEN A.Label_Ko ELSE A.Label_En END Label_Nm,
              'L' AS Div,
              ISNULL(B.Pgm_Id, 'N/A') AS Pgm_Id
         FROM UPDMS_COM_LABEL AS A WITH(NOLOCK)
         LEFT OUTER JOIN UPDMS_COM_PGM_LABEL AS B WITH(NOLOCK) ON A.Seq = B.Label_Id
        WHERE A.Seq = @li_label_id

       UNION ALL

       SELECT DISTINCT CASE @ls_lang_set WHEN 'ko' THEN A.Label_Ko ELSE A.Label_En END Label_Nm,
              'G',
              ISNULL(C.Pgm_Id, 'N/A')
         FROM UPDMS_COM_LABEL AS A WITH(NOLOCK)
         LEFT OUTER JOIN UPDMS_COM_PGM_GRID_COLUMN AS C WITH(NOLOCK) ON A.Seq = C.Label_Id
        WHERE A.Seq = @li_label_id
       ) Y
    ON X.Pgm_Id = Y.Pgm_Id
 ORDER BY Y.Div, Y.Pgm_Id

GO